package com.fuqi.android.zhongcai.sql.dao;

import android.content.Context;
import android.database.Cursor;
import android.util.Log;

import com.fuqi.android.zhongcai.sql.CacheImg;
import com.fuqi.android.zhongcai.sql.CacheImgDao;
import com.fuqi.android.zhongcai.sql.DaoMaster;
import com.fuqi.android.zhongcai.sql.DaoSession;
import com.fuqi.android.zhongcai.sql.Dic;
import com.fuqi.android.zhongcai.sql.DicDao;
import com.fuqi.android.zhongcai.sql.Worker;
import com.fuqi.android.zhongcai.sql.WorkerDao;
import com.fuqi.android.zhongcai.utils.WorkerSort;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import de.greenrobot.dao.query.DeleteQuery;
import de.greenrobot.dao.query.Query;
import de.greenrobot.dao.query.QueryBuilder;

/**
 * Created by juner on 16/09/28.
 * 数据库操作
 */
public class SQLDao {


    // 数据库名字
    public static final String DB_NAME = "mly_db";

    private static WorkerDao workerDao;
    private static DicDao dicao;
    private static CacheImgDao cacheDao;
    private static DaoSession daoSession;
    private static SQLDao sqlDao = null;

    private SQLDao(Context context) {

        initDb(context);

    }


    public static SQLDao getInstance(Context context) {

        if (sqlDao == null) {
            sqlDao = new SQLDao(context);
        }

        return sqlDao;

    }


    /**
     * 通过关键字或者拼音搜索
     *
     * @param keyword
     * @return
     */
    public static List<Worker> searchWorkerByKeyword(final String keyword) {

        Cursor cursor = daoSession.getDatabase().rawQuery("select * from " + WorkerDao.TABLENAME + " where name like \"%" + keyword
                + "%\" or pinyin like \"%" + keyword + "%\"", null);
        List<Worker> result = new ArrayList<>();
        Worker worker;
        while (cursor.moveToNext()) {
            String uid = cursor.getString(cursor.getColumnIndex(WorkerDao.Properties.Uid.columnName));
            String name = cursor.getString(cursor.getColumnIndex(WorkerDao.Properties.Name.columnName));
            String pinyin = cursor.getString(cursor.getColumnIndex(WorkerDao.Properties.Pinyin.columnName));
            String phone = cursor.getString(cursor.getColumnIndex(WorkerDao.Properties.Phone.columnName));

            worker = new Worker(uid,name, pinyin,phone);
            result.add(worker);
        }
        cursor.close();
        Collections.sort(result, new WorkerSort());
        return result;
    }

    public List<Worker> getAllWorker() {
        try{
            Query query = workerDao.queryBuilder().orderAsc(WorkerDao.Properties.Pinyin).build();
            return query.list();
        }catch (Exception e){}
        return null;
    }

    public void insertWorker(Worker worker) {
        workerDao.insert(worker);
    }

    public void insertWorker(List<Worker> worker) {
        workerDao.insertOrReplaceInTx(worker);
    }

    public void clearWorker() {

        workerDao.deleteAll();

    }

    public void clearDic(String DNSYType) {
        QueryBuilder<Dic> qb = dicao.queryBuilder();
        DeleteQuery<Dic> bd = qb.where(DicDao.Properties.Pbianma.like(DNSYType+"%")).buildDelete();
        bd.executeDeleteWithoutDetachingEntities();
    }

    private void initDb(Context context) {

        DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(context, DB_NAME, null);
        DaoMaster daoMaster = new DaoMaster(helper.getReadableDatabase());
        daoSession = daoMaster.newSession();
        workerDao = daoSession.getWorkerDao();
        dicao = daoSession.getDicDao();
        cacheDao = daoSession.getCacheImgDao();
    }

    public void deleteAll(){
        dicao.deleteAll();
        workerDao.deleteAll();
    }


    public void insertDic(Dic[] dic){
//        dicao.insertOrReplace(dic);
        dicao.insertOrReplaceInTx(dic);
    }

    /**
     * 根据字典pbianma匹配字典
     * @param DNSYType
     * @return
     */
    public List<Dic> selectDNSY(String DNSYType){
        try{
            Query query = dicao.queryBuilder().where(DicDao.Properties.Pbianma.like(DNSYType+"%")).build();
            return query.list();
        }catch (Exception e){}
        return null;
    }

    /**
     * 根据字典pbianma查询字典
     * @param DNSYType
     * @return
     */
    public Dic selectWithType(String DNSYType){

        try {
            Dic dic = null;
            Query query = dicao.queryBuilder().where(DicDao.Properties.Pbianma.eq(DNSYType)).build();
            if(query.list().size() > 0)
                dic = (Dic) query.list().get(0);
            return dic;
        }catch (Exception e){}
        return null;
    }

    /**
     * 根据产品id查询产品名称
     * @param DNSYType
     * @return
     */
    public Dic selectProWithType(String DNSYType){
        try {
            Dic dic = null;
            Query query = dicao.queryBuilder().where(DicDao.Properties.Id.eq(DNSYType)).build();
            if (query.list().size() > 0)
                dic = (Dic) query.list().get(0);
            return dic;
        }catch (Exception e){}
        return null;
    }

    /**
     * 查询是否有缓存
     * @return
     */
    public int selectCacheCount(){
        try {
            int count = 0;
            Query query = cacheDao.queryBuilder().build();
            return query.list().size();
        }catch (Exception e){}
        return 0;
    }

    /**
     * 查询缓存列表
     * @return
     */
    public List<CacheImg> selectCacheList(){
        try {
            Query query = cacheDao.queryBuilder().build();
            return query.list();
        }catch (Exception e){}
        return null;
    }

    /**
     * 根据产品id查询缓存
     * @param ordid
     * @return
     */
    public CacheImg selectCacheWithOrdid(String ordid){
        try {
            CacheImg cacheImg = null;
            Query query = cacheDao.queryBuilder().where(CacheImgDao.Properties.Ordid.eq(ordid)).build();
            if (query.list().size() > 0)
                cacheImg = (CacheImg) query.list().get(0);
            return cacheImg;
        }catch (Exception e){}
        return null;
    }

    /**
     * 根据产品id删除缓存
     * @param ordid
     * @return
     */
    public void delCacheWithOrdid(String ordid){
        String sql = "delete from %s where %s = '%s'";
        String ss = String.format(sql,CacheImgDao.TABLENAME , CacheImgDao.Properties.Ordid.columnName , ordid);
        Log.e("ccqx","sql"+ss);
        daoSession.getDatabase().execSQL(ss);
    }

    public void insertDic(CacheImg cacheImg){
        cacheDao.insert(cacheImg);
    }
}
